package com.kayak.controller;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import com.kayak.core.Sql;
import com.kayak.enums.Dialect;
import com.kayak.jdbc.JdbcConnection;
import com.kayak.pojo.ColumnObj;
import com.kayak.pojo.TableObj;

import jxl.Sheet;
import jxl.Workbook;

@Controller
@RequestMapping
public class IndexController {
	 public static final Logger LOGGER = Logger.getLogger(IndexController.class);
	 
	 /*
	  * 导入页面入口
	  * */
	 @RequestMapping(value = "/index")
	 public String index(){
		 return "input";
	 }
	 
	 /*
	  * 读取Excel文件
	  * */
	 @RequestMapping(value = "/implExcel")
	 public Object implxcel(@RequestParam(value = "file",required = false) MultipartFile file,ModelMap model) {
		 try {
			 //得到上传的文件名
			String fileName = file.getOriginalFilename();
			String suffix = fileName.substring(fileName.lastIndexOf(".")+1);
			//根据文件输入流导入Excel从而产生Workbook对象
			Workbook workbook = null;
			if (suffix.equals("xls")) {
				workbook = Workbook.getWorkbook(file.getInputStream());;
			}else{
				model.addAttribute("msg","请选择正确格式Excel");
				return "result";
			}
			//总的sheet个数
			int sheetNumber = workbook.getNumberOfSheets();
			
			for(int i = 0; i < sheetNumber; i++){
			//	Sheet sheet = workbook.getSheet(i);
			//System.out.println(sheet.toString());
				TableObj tableObj =readSheet(workbook.getSheet(i));
				String getSql = createTableByMysql(tableObj);
				executeMySql(getSql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		 model.addAttribute("msg","读取成功!");
	     return "result";
	 }
	 
	 /*
	  * 解析Excel表单
	  * */
	 private TableObj readSheet(Sheet sheet){
		 ColumnObj columnObj = null;
		 TableObj sql = new TableObj();
		 List<ColumnObj> columnArray = new ArrayList<>(50);
	/*	for(Label label : sheet){
			
		 }*/
		 for(int i=0;i<sheet.getRows();i++){
	            for(int j=0;j<sheet.getColumns();j++){
	            	if (i == 0) {
	            		sql.setTableDetail(sheet.getCell(0, 0).getContents());
	            		continue;
	            	}
	            	if (i == 1) {
	            		sql.setTableName(sheet.getCell(1, 0).getContents());
	            		continue;
	            	}
	            	if (i == 2) continue;
	   	
            		//Cell cell = sheet.getCell(j, i);
            		columnObj = new ColumnObj();
            		columnObj.setColumnName(sheet.getCell(i, 0).getContents());
            		columnObj.setFieldName(sheet.getCell(i, 1).getContents());
            		columnObj.setColumnRemark(sheet.getCell(i, 2).getContents());
            		columnObj.setFieldType(sheet.getCell(i, 3).getContents());
            		columnObj.setLength(sheet.getCell(i, 4).getContents());
            		columnObj.setIsEmpty(sheet.getCell(i, 5).getContents().equals("YES"));
            		columnArray.add(columnObj);		
	            }
	      }
		 sql.setColumnArray(columnArray);      
		return sql;
	 }
	 /*
	  * 创建Sql语句
	  * */
	 private String createTableByMysql(TableObj tableObj){
		 StringBuilder stb = new StringBuilder();
		 stb.append("CREATE TABLE").append(tableObj.getTableName()).append("(");
		 for (ColumnObj columnObj : tableObj.getColumnArray()) {
			 stb.append(new Sql(columnObj, Dialect.MYSQL).getSql()).append(",");
		 }
		 stb.delete(stb.length()-1, stb.length());
		 stb.append(");");
		 System.out.println(stb.toString());
		 return stb.toString();
	 }
	 /*
	     * 执行Sql语句
	     * */
	    private void executeMySql(String getSql){
	    	   JdbcConnection jdbcConnection = new JdbcConnection();
	           jdbcConnection.creactSql(getSql);
	    }
}












































